package org.mvn_spring_jsf_jar.excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.mvn_spring_jsf_jar.model.Cliente;

public class ExportCliente {

	@SuppressWarnings({ "deprecation", "rawtypes" })
	public static List<Cliente> getClienteFromExcel(String filename) {

		List<Cliente> clienteList = new ArrayList<Cliente>();

		FileInputStream fis = null;
		try {
			//
			// Create a FileInputStream that will be use to read the
			// excel file.
			//
			fis = new FileInputStream(filename);

			//
			// Create an excel workbook from the file system.
			//
			HSSFWorkbook workbook = new HSSFWorkbook(fis);
			//
			// Get the first sheet on the workbook.
			//
			HSSFSheet sheet = workbook.getSheetAt(0);

			//
			// Hay que tener en cuenta el formato de la celda. que este en modo texto
			//
			Iterator rows = sheet.rowIterator();
			while (rows.hasNext()) {
				HSSFRow row = (HSSFRow) rows.next();

				HSSFCell cell1 = row.getCell((short) 0);
				String nombre = cell1.getStringCellValue();

				HSSFCell cell2 = row.getCell((short) 1);
				String numeroTarjeta = cell2.getStringCellValue();
				
				HSSFCell cell3 = row.getCell((short) 2);
				Double monto = cell3.getNumericCellValue();
				BigDecimal montoACobrar = new BigDecimal(monto);

				clienteList.add(new Cliente(null, nombre, null, numeroTarjeta,montoACobrar));
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fis != null) {
				try {
					fis.close();
				} catch (IOException e) {
				}
			}
		}

		return clienteList;
	}

	public static void main(String[] args) {
		Long id = 2000000L;
		Long contador = 0L;
		StringBuilder sb = new StringBuilder();
		List<Cliente> list = ExportCliente
				.getClienteFromExcel("/home/COTESA/prodriguez/Documents/clientes.xls");
		for (Cliente cliente : list) {

			sb.append(
					"INSERT INTO sixsigma.cliente(id,nombre,numerotarjeta,montoacobrar) values(")
					.append(id + contador + ",'" + cliente.getNombre() + "','"
							+ cliente.getNumeroTarjeta() + "',"
							+ cliente.getMontoACobrar() +");\n");
			contador++;
		}

		System.out.println(sb.toString());

	}
}
